Imports Microsoft.VisualBasic
Imports DAOMain
Imports System.Data
Imports System.Data.OleDb
Imports System.Configuration.ConfigurationSettings
Imports System.Diagnostics

Namespace DAOAdmin

#Region "frequency_mstr"

    <Serializable()> _
    Public Class FREQUENCY_MSTR_VO

        Public FREQUENCY_CD As String
        Public FREQUENCY_DESC As String
        Public FREQUENCY_COUNT As Integer = -1
        Public FREQUENCY_CREA_BY As String
        Public FREQUENCY_CREA_DT As Date
        Public FREQUENCY_LAST_UPD_BY As String
        Public FREQUENCY_LAST_UPD_DT As Date

        Public Enum Enum_FREQUENCY_MSTR As Short
            NONE = 0
            frequency_cd = 1
            frequency_desc = 2
            frequency_count = 4
            frequency_crea_by = 8
            frequency_crea_dt = 16
            frequency_last_upd_by = 32
            frequency_last_upd_dt = 64
        End Enum

    End Class

    Public Class FREQUENCY_MSTR_DAO
        Inherits CommonDAO

        Public Function Search(ByVal Value As FREQUENCY_MSTR_VO, Optional ByVal ExcludeValue As FREQUENCY_MSTR_VO.Enum_FREQUENCY_MSTR = 0, Optional ByVal SQLLike As Boolean = False) As Data.DataTable
            Dim daoMain As New Polydao.Common
            Dim cnnSearch As New OleDbConnection
            Dim dtSearch As New Data.DataTable

            Try
                daoMain.ConnectionString = Me.ConnectionString
                cnnSearch = daoMain.OpenConnection

                Dim cmdSearch As New OleDbCommand

                With cmdSearch
                    .Connection = cnnSearch
                    .CommandText = "SELECT * FROM frequency_mstr " & QueryBuilder(Value, QueryType.SelectType, ExcludeValue, , SQLLike)
                End With

                Call ParamBuilder(cmdSearch, Value, ExcludeValue)

                Dim daSearch As New OleDbDataAdapter(cmdSearch)

                daSearch.Fill(dtSearch)

            Catch ex As Exception

                Debug.WriteLine(ex.Message)

            Finally
                daoMain.CloseConnection(cnnSearch)

            End Try

            Return dtSearch
        End Function

        Private Function QueryBuilder(ByVal Value As FREQUENCY_MSTR_VO, Optional ByVal QueryType As QueryType = 0, _
         Optional ByVal ExcludeFlag As FREQUENCY_MSTR_VO.Enum_FREQUENCY_MSTR = FREQUENCY_MSTR_VO.Enum_FREQUENCY_MSTR.NONE, Optional ByVal TableAlias As String = "", Optional ByVal SQLLike As Boolean = False, _
         Optional ByVal IncludeBlanks As Boolean = True) As String

            Dim sbQuery As New StringBuilder
            Dim shrtFlag As Short

            With Value
                If Not IsNothing(.FREQUENCY_CD) Then shrtFlag += FREQUENCY_MSTR_VO.Enum_FREQUENCY_MSTR.frequency_cd
                If Not IsNothing(.FREQUENCY_DESC) Then shrtFlag += FREQUENCY_MSTR_VO.Enum_FREQUENCY_MSTR.frequency_desc
                If .FREQUENCY_COUNT >= 0 Then shrtFlag += FREQUENCY_MSTR_VO.Enum_FREQUENCY_MSTR.frequency_count
                If Not IsNothing(.FREQUENCY_CREA_BY) Then shrtFlag += FREQUENCY_MSTR_VO.Enum_FREQUENCY_MSTR.frequency_crea_by
                If Year(.FREQUENCY_CREA_DT) > 1 Then shrtFlag += FREQUENCY_MSTR_VO.Enum_FREQUENCY_MSTR.frequency_crea_dt
                If Not IsNothing(.FREQUENCY_LAST_UPD_BY) Then shrtFlag += FREQUENCY_MSTR_VO.Enum_FREQUENCY_MSTR.frequency_last_upd_by
                If Year(.FREQUENCY_LAST_UPD_DT) > 1 Then shrtFlag += FREQUENCY_MSTR_VO.Enum_FREQUENCY_MSTR.frequency_last_upd_dt
            End With
            If shrtFlag <> 0 Then
                Dim bytCount As Byte

                If ExcludeFlag <> FREQUENCY_MSTR_VO.Enum_FREQUENCY_MSTR.NONE Then shrtFlag -= ExcludeFlag
                With sbQuery

                    Select Case QueryType
                        Case QueryType.SelectType  ' Where
                            .Append("WHERE ")
                        Case QueryType.UpdateType  'Update
                            .Append("UPDATE frequency_mstr SET ")
                        Case QueryType.InsertType  'Insert
                            .Append("INSERT INTO frequency_mstr (")
                        Case QueryType.DeleteType   'Delete
                            .Append("DELETE FROM frequency_mstr WHERE ")
                    End Select

                    Do While shrtFlag > 0
                        If CheckBit(shrtFlag, FREQUENCY_MSTR_VO.Enum_FREQUENCY_MSTR.frequency_cd) Then
                            .Append("FREQUENCY_CD")
                            shrtFlag -= FREQUENCY_MSTR_VO.Enum_FREQUENCY_MSTR.frequency_cd

                        ElseIf CheckBit(shrtFlag, FREQUENCY_MSTR_VO.Enum_FREQUENCY_MSTR.frequency_desc) Then
                            .Append("FREQUENCY_DESC")
                            shrtFlag -= FREQUENCY_MSTR_VO.Enum_FREQUENCY_MSTR.frequency_desc

                        ElseIf CheckBit(shrtFlag, FREQUENCY_MSTR_VO.Enum_FREQUENCY_MSTR.frequency_count) Then
                            .Append("FREQUENCY_COUNT")
                            shrtFlag -= FREQUENCY_MSTR_VO.Enum_FREQUENCY_MSTR.frequency_count

                        ElseIf CheckBit(shrtFlag, FREQUENCY_MSTR_VO.Enum_FREQUENCY_MSTR.frequency_crea_by) Then
                            .Append("FREQUENCY_CREA_BY")
                            shrtFlag -= FREQUENCY_MSTR_VO.Enum_FREQUENCY_MSTR.frequency_crea_by

                        ElseIf CheckBit(shrtFlag, FREQUENCY_MSTR_VO.Enum_FREQUENCY_MSTR.frequency_crea_dt) Then
                            .Append("FREQUENCY_CREA_DT")
                            shrtFlag -= FREQUENCY_MSTR_VO.Enum_FREQUENCY_MSTR.frequency_crea_dt

                        ElseIf CheckBit(shrtFlag, FREQUENCY_MSTR_VO.Enum_FREQUENCY_MSTR.frequency_last_upd_by) Then
                            .Append("FREQUENCY_LAST_UPD_BY")
                            shrtFlag -= FREQUENCY_MSTR_VO.Enum_FREQUENCY_MSTR.frequency_last_upd_by

                        ElseIf CheckBit(shrtFlag, FREQUENCY_MSTR_VO.Enum_FREQUENCY_MSTR.frequency_last_upd_dt) Then
                            .Append("FREQUENCY_LAST_UPD_DT")
                            shrtFlag -= FREQUENCY_MSTR_VO.Enum_FREQUENCY_MSTR.frequency_last_upd_dt

                        End If

                        Select Case QueryType
                            Case QueryType.SelectType, QueryType.DeleteType  ' Where Clause
                                Select Case SQLLike
                                    Case False
                                        .Append(" = ? AND ")

                                    Case True
                                        .Append(" LIKE ? AND ")

                                End Select

                            Case QueryType.UpdateType  ' Update
                                .Append(" = ?, ")

                            Case QueryType.InsertType  ' Insert
                                .Append(", ")
                                bytCount += 1

                            Case Else 'bypass
                                .Append(" AND ")
                                QueryType = 0

                        End Select
                    Loop

                    Select Case QueryType
                        Case QueryType.SelectType, QueryType.DeleteType  'Where Clause
                            .Replace(" AND ", "", .Length - 6, 6)
                            .Append(";")

                        Case QueryType.UpdateType   ' Update
                            .Replace("?, ", "? ", .Length - 3, 3)

                        Case QueryType.InsertType  ' Insert
                            .Replace(", ", ") VALUES (", .Length - 2, 2)
                            .Append("#", bytCount)
                            .Replace("#", "?,")
                            .Replace("?,", "?);", .Length - 2, 2)
                    End Select
                End With
            End If
            Return sbQuery.ToString
        End Function

        Private Sub ParamBuilder(ByRef Command As OleDbCommand, ByVal Value As FREQUENCY_MSTR_VO, Optional ByVal QueryType As QueryType = 0, Optional ByVal ExcludeValue As FREQUENCY_MSTR_VO.Enum_FREQUENCY_MSTR = FREQUENCY_MSTR_VO.Enum_FREQUENCY_MSTR.NONE)
            With Command.Parameters
                If Not IsNothing(Value.FREQUENCY_CD) And Not CheckBit(ExcludeValue, FREQUENCY_MSTR_VO.Enum_FREQUENCY_MSTR.frequency_cd) Then .AddWithValue("@frequency_cd", Value.FREQUENCY_CD)
                If Not IsNothing(Value.FREQUENCY_DESC) And Not CheckBit(ExcludeValue, FREQUENCY_MSTR_VO.Enum_FREQUENCY_MSTR.frequency_desc) Then .AddWithValue("@frequency_desc", Value.FREQUENCY_DESC)
                If Value.FREQUENCY_COUNT >= 0 And Not CheckBit(ExcludeValue, FREQUENCY_MSTR_VO.Enum_FREQUENCY_MSTR.frequency_count) Then .AddWithValue("@frequency_count", Value.FREQUENCY_COUNT)
                If Not IsNothing(Value.FREQUENCY_CREA_BY) And Not CheckBit(ExcludeValue, FREQUENCY_MSTR_VO.Enum_FREQUENCY_MSTR.frequency_crea_by) Then .AddWithValue("@frequency_crea_by", Value.FREQUENCY_CREA_BY)
                If Year(Value.FREQUENCY_CREA_DT) > 1 And Not CheckBit(ExcludeValue, FREQUENCY_MSTR_VO.Enum_FREQUENCY_MSTR.frequency_crea_dt) Then .AddWithValue("@frequency_crea_dt", Value.FREQUENCY_CREA_DT)
                If Not IsNothing(Value.FREQUENCY_LAST_UPD_BY) And Not CheckBit(ExcludeValue, FREQUENCY_MSTR_VO.Enum_FREQUENCY_MSTR.frequency_last_upd_by) Then .AddWithValue("@frequency_last_upd_by", Value.FREQUENCY_LAST_UPD_BY)
                If Year(Value.FREQUENCY_LAST_UPD_DT) > 1 And Not CheckBit(ExcludeValue, FREQUENCY_MSTR_VO.Enum_FREQUENCY_MSTR.frequency_last_upd_dt) Then .AddWithValue("@frequency_last_upd_dt", Value.FREQUENCY_LAST_UPD_DT)
            End With
        End Sub
        Public Function GetQuery(ByVal Value As FREQUENCY_MSTR_VO, Optional ByVal ExcludeValue As FREQUENCY_MSTR_VO.Enum_FREQUENCY_MSTR = 0, Optional ByVal SQLLike As Boolean = False) As String
            Dim strReturn As String = "SELECT * FROM frequency_mstr " & QueryBuilder(Value, QueryType.SelectType, ExcludeValue, , SQLLike)
            Return strReturn
        End Function

    End Class


#End Region

#Region "user_mstr"

    <Serializable()> _
    Public Class USER_MSTR_VO

        Public USER_NAME As String
        Public USER_PASSWORD As String
        Public USER_LAST_NAME As String
        Public USER_FIRST_NAME As String
        Public USER_MIDDLE_NAME As String
        Public DEPT_CD As String
        Public ROLE_CD As String
        Public ADVISOR_TAG As Integer = -1
        Public ADVISOR_LEVEL_CD As String
        Public USER_ADDRESS As String
        Public USER_ADDRESS2 As String
        Public CITY_CD As String
        Public COUNTRY_CD As String
        Public ZIP_CD As String
        Public TEL_NO As String
        Public FAX_NO As String
        Public MOBILE_NO As String
        Public EMAIL_ADDRESS As String
        Public REMARKS As String
        Public USER_CREA_BY As String
        Public USER_CREA_DT As Date
        Public USER_LAST_UPD_BY As String
        Public USER_LAST_UPD_DT As Date

        Public Enum Enum_user_mstr As Integer
            NONE = 0
            user_name = 1
            user_password = 2
            user_last_name = 4
            user_first_name = 8
            user_middle_name = 16
            dept_cd = 32
            role_cd = 64
            advisor_tag = 128
            advisor_level_cd = 256
            user_address = 512
            user_address2 = 1024
            city_cd = 2048
            country_cd = 4096
            zip_cd = 8192
            tel_no = 16384
            fax_no = 32768
            mobile_no = 65536
            email_address = 131072
            remarks = 262144
            user_crea_by = 524288
            user_crea_dt = 1048576
            user_last_upd_by = 2097152
            user_last_upd_dt = 4194304
        End Enum

    End Class

    Public Class USER_MSTR_DAO
        Inherits CommonDAO

        Public Function Search(ByVal Value As user_mstr_VO, Optional ByVal ExcludeValue As user_mstr_VO.Enum_user_mstr = 0, Optional ByVal SQLLike As Boolean = False) As Data.Datatable
            Dim daoMain As New Polydao.Common
            Dim cnnSearch As New OleDbConnection
            Dim dtSearch As New Data.DataTable

            Try
                daoMain.ConnectionString = Me.ConnectionString
                cnnSearch = daoMain.OpenConnection

                Dim cmdSearch As New OLEDBCommand

                With cmdSearch
                    .Connection = cnnSearch
                    .CommandText = "SELECT * FROM user_mstr " & QueryBuilder(Value, QueryType.SelectType, ExcludeValue, , SQLLike)
                End With

                Call ParamBuilder(cmdSearch, Value, ExcludeValue)

                Dim daSearch As New OleDbDataAdapter(cmdSearch)

                daSearch.Fill(dtSearch)

            Catch ex As Exception

                Debug.WriteLine(ex.Message)

            Finally
                daoMain.CloseConnection(cnnSearch)

            End Try

            Return dtSearch
        End Function

        Private Function QueryBuilder(ByVal Value As USER_MSTR_VO, Optional ByVal QueryType As QueryType = 0, _
         Optional ByVal ExcludeFlag As USER_MSTR_VO.Enum_user_mstr = USER_MSTR_VO.Enum_user_mstr.NONE, Optional ByVal TableAlias As String = "", Optional ByVal SQLLike As Boolean = False, _
         Optional ByVal IncludeBlanks As Boolean = True) As String

            Dim sbQuery As New StringBuilder
            Dim shrtFlag As Integer

            With Value
                If Not isnothing(.USER_NAME) Then shrtFlag += USER_MSTR_VO.Enum_user_mstr.USER_NAME
                If Not isnothing(.USER_PASSWORD) Then shrtFlag += USER_MSTR_VO.Enum_user_mstr.USER_PASSWORD
                If Not isnothing(.USER_LAST_NAME) Then shrtFlag += USER_MSTR_VO.Enum_user_mstr.USER_LAST_NAME
                If Not isnothing(.USER_FIRST_NAME) Then shrtFlag += USER_MSTR_VO.Enum_user_mstr.USER_FIRST_NAME
                If Not isnothing(.USER_MIDDLE_NAME) Then shrtFlag += USER_MSTR_VO.Enum_user_mstr.USER_MIDDLE_NAME
                If Not isnothing(.DEPT_CD) Then shrtFlag += USER_MSTR_VO.Enum_user_mstr.DEPT_CD
                If Not isnothing(.ROLE_CD) Then shrtFlag += USER_MSTR_VO.Enum_user_mstr.ROLE_CD
                If .ADVISOR_TAG >= 0 Then shrtFlag += USER_MSTR_VO.Enum_user_mstr.ADVISOR_TAG
                If Not isnothing(.ADVISOR_LEVEL_CD) Then shrtFlag += USER_MSTR_VO.Enum_user_mstr.ADVISOR_LEVEL_CD
                If Not isnothing(.USER_ADDRESS) Then shrtFlag += USER_MSTR_VO.Enum_user_mstr.USER_ADDRESS
                If Not isnothing(.USER_ADDRESS2) Then shrtFlag += USER_MSTR_VO.Enum_user_mstr.USER_ADDRESS2
                If Not isnothing(.CITY_CD) Then shrtFlag += USER_MSTR_VO.Enum_user_mstr.CITY_CD
                If Not isnothing(.COUNTRY_CD) Then shrtFlag += USER_MSTR_VO.Enum_user_mstr.COUNTRY_CD
                If Not isnothing(.ZIP_CD) Then shrtFlag += USER_MSTR_VO.Enum_user_mstr.ZIP_CD
                If Not isnothing(.TEL_NO) Then shrtFlag += USER_MSTR_VO.Enum_user_mstr.TEL_NO
                If Not isnothing(.FAX_NO) Then shrtFlag += USER_MSTR_VO.Enum_user_mstr.FAX_NO
                If Not isnothing(.MOBILE_NO) Then shrtFlag += USER_MSTR_VO.Enum_user_mstr.MOBILE_NO
                If Not isnothing(.EMAIL_ADDRESS) Then shrtFlag += USER_MSTR_VO.Enum_user_mstr.EMAIL_ADDRESS
                If Not isnothing(.REMARKS) Then shrtFlag += USER_MSTR_VO.Enum_user_mstr.REMARKS
                If Not isnothing(.USER_CREA_BY) Then shrtFlag += USER_MSTR_VO.Enum_user_mstr.USER_CREA_BY
                If Year(.USER_CREA_DT) > 1 Then shrtFlag += USER_MSTR_VO.Enum_user_mstr.USER_CREA_DT
                If Not isnothing(.USER_LAST_UPD_BY) Then shrtFlag += USER_MSTR_VO.Enum_user_mstr.USER_LAST_UPD_BY
                If Year(.USER_LAST_UPD_DT) > 1 Then shrtFlag += USER_MSTR_VO.Enum_user_mstr.USER_LAST_UPD_DT
            End With
            If shrtFlag <> 0 Then
                Dim bytCount As Byte

                If ExcludeFlag <> USER_MSTR_VO.Enum_user_mstr.None Then shrtFlag -= ExcludeFlag
                With sbQuery

                    Select Case QueryType
                        Case QueryType.SelectType  ' Where
                            .Append("WHERE ")
                        Case QueryType.UpdateType  'Update
                            .Append("UPDATE user_mstr SET ")
                        Case QueryType.InsertType  'Insert
                            .Append("INSERT INTO user_mstr (")
                        Case QueryType.DeleteType   'Delete
                            .Append("DELETE FROM user_mstr WHERE ")
                    End Select

                    Do While shrtFlag > 0
                        If CheckBit(shrtFlag, USER_MSTR_VO.Enum_user_mstr.USER_NAME) Then
                            .Append("USER_NAME")
                            shrtFlag -= USER_MSTR_VO.Enum_user_mstr.USER_NAME

                        ElseIf CheckBit(shrtFlag, USER_MSTR_VO.Enum_user_mstr.USER_PASSWORD) Then
                            .Append("USER_PASSWORD")
                            shrtFlag -= USER_MSTR_VO.Enum_user_mstr.USER_PASSWORD

                        ElseIf CheckBit(shrtFlag, USER_MSTR_VO.Enum_user_mstr.USER_LAST_NAME) Then
                            .Append("USER_LAST_NAME")
                            shrtFlag -= USER_MSTR_VO.Enum_user_mstr.USER_LAST_NAME

                        ElseIf CheckBit(shrtFlag, USER_MSTR_VO.Enum_user_mstr.USER_FIRST_NAME) Then
                            .Append("USER_FIRST_NAME")
                            shrtFlag -= USER_MSTR_VO.Enum_user_mstr.USER_FIRST_NAME

                        ElseIf CheckBit(shrtFlag, USER_MSTR_VO.Enum_user_mstr.USER_MIDDLE_NAME) Then
                            .Append("USER_MIDDLE_NAME")
                            shrtFlag -= USER_MSTR_VO.Enum_user_mstr.USER_MIDDLE_NAME

                        ElseIf CheckBit(shrtFlag, USER_MSTR_VO.Enum_user_mstr.DEPT_CD) Then
                            .Append("DEPT_CD")
                            shrtFlag -= USER_MSTR_VO.Enum_user_mstr.DEPT_CD

                        ElseIf CheckBit(shrtFlag, USER_MSTR_VO.Enum_user_mstr.ROLE_CD) Then
                            .Append("ROLE_CD")
                            shrtFlag -= USER_MSTR_VO.Enum_user_mstr.ROLE_CD

                        ElseIf CheckBit(shrtFlag, USER_MSTR_VO.Enum_user_mstr.ADVISOR_TAG) Then
                            .Append("ADVISOR_TAG")
                            shrtFlag -= USER_MSTR_VO.Enum_user_mstr.ADVISOR_TAG

                        ElseIf CheckBit(shrtFlag, USER_MSTR_VO.Enum_user_mstr.ADVISOR_LEVEL_CD) Then
                            .Append("ADVISOR_LEVEL_CD")
                            shrtFlag -= USER_MSTR_VO.Enum_user_mstr.ADVISOR_LEVEL_CD

                        ElseIf CheckBit(shrtFlag, USER_MSTR_VO.Enum_user_mstr.USER_ADDRESS) Then
                            .Append("USER_ADDRESS")
                            shrtFlag -= USER_MSTR_VO.Enum_user_mstr.USER_ADDRESS

                        ElseIf CheckBit(shrtFlag, USER_MSTR_VO.Enum_user_mstr.USER_ADDRESS2) Then
                            .Append("USER_ADDRESS2")
                            shrtFlag -= USER_MSTR_VO.Enum_user_mstr.USER_ADDRESS2

                        ElseIf CheckBit(shrtFlag, USER_MSTR_VO.Enum_user_mstr.CITY_CD) Then
                            .Append("CITY_CD")
                            shrtFlag -= USER_MSTR_VO.Enum_user_mstr.CITY_CD

                        ElseIf CheckBit(shrtFlag, USER_MSTR_VO.Enum_user_mstr.COUNTRY_CD) Then
                            .Append("COUNTRY_CD")
                            shrtFlag -= USER_MSTR_VO.Enum_user_mstr.COUNTRY_CD

                        ElseIf CheckBit(shrtFlag, USER_MSTR_VO.Enum_user_mstr.ZIP_CD) Then
                            .Append("ZIP_CD")
                            shrtFlag -= USER_MSTR_VO.Enum_user_mstr.ZIP_CD

                        ElseIf CheckBit(shrtFlag, USER_MSTR_VO.Enum_user_mstr.TEL_NO) Then
                            .Append("TEL_NO")
                            shrtFlag -= USER_MSTR_VO.Enum_user_mstr.TEL_NO

                        ElseIf CheckBit(shrtFlag, USER_MSTR_VO.Enum_user_mstr.FAX_NO) Then
                            .Append("FAX_NO")
                            shrtFlag -= USER_MSTR_VO.Enum_user_mstr.FAX_NO

                        ElseIf CheckBit(shrtFlag, USER_MSTR_VO.Enum_user_mstr.MOBILE_NO) Then
                            .Append("MOBILE_NO")
                            shrtFlag -= USER_MSTR_VO.Enum_user_mstr.MOBILE_NO

                        ElseIf CheckBit(shrtFlag, USER_MSTR_VO.Enum_user_mstr.EMAIL_ADDRESS) Then
                            .Append("EMAIL_ADDRESS")
                            shrtFlag -= USER_MSTR_VO.Enum_user_mstr.EMAIL_ADDRESS

                        ElseIf CheckBit(shrtFlag, USER_MSTR_VO.Enum_user_mstr.REMARKS) Then
                            .Append("REMARKS")
                            shrtFlag -= USER_MSTR_VO.Enum_user_mstr.REMARKS

                        ElseIf CheckBit(shrtFlag, USER_MSTR_VO.Enum_user_mstr.USER_CREA_BY) Then
                            .Append("USER_CREA_BY")
                            shrtFlag -= USER_MSTR_VO.Enum_user_mstr.USER_CREA_BY

                        ElseIf CheckBit(shrtFlag, USER_MSTR_VO.Enum_user_mstr.USER_CREA_DT) Then
                            .Append("USER_CREA_DT")
                            shrtFlag -= USER_MSTR_VO.Enum_user_mstr.USER_CREA_DT

                        ElseIf CheckBit(shrtFlag, USER_MSTR_VO.Enum_user_mstr.USER_LAST_UPD_BY) Then
                            .Append("USER_LAST_UPD_BY")
                            shrtFlag -= USER_MSTR_VO.Enum_user_mstr.USER_LAST_UPD_BY

                        ElseIf CheckBit(shrtFlag, USER_MSTR_VO.Enum_user_mstr.USER_LAST_UPD_DT) Then
                            .Append("USER_LAST_UPD_DT")
                            shrtFlag -= USER_MSTR_VO.Enum_user_mstr.USER_LAST_UPD_DT

                        End If

                        Select Case QueryType
                            Case QueryType.SelectType, QueryType.DeleteType  ' Where Clause
                                Select Case SQLLike
                                    Case False
                                        .Append(" = ? AND ")

                                    Case True
                                        .Append(" LIKE ? AND ")

                                End Select

                            Case QueryType.UpdateType  ' Update
                                .Append(" = ?, ")

                            Case QueryType.InsertType  ' Insert
                                .Append(", ")
                                bytCount += 1

                            Case Else 'bypass
                                .Append(" AND ")
                                QueryType = 0

                        End Select
                    Loop

                    Select Case QueryType
                        Case QueryType.SelectType, QueryType.DeleteType  'Where Clause
                            .Replace(" AND ", "", .Length - 6, 6)
                            .Append(";")

                        Case QueryType.UpdateType   ' Update
                            .Replace("?, ", "? ", .Length - 3, 3)

                        Case QueryType.InsertType  ' Insert
                            .Replace(", ", ") VALUES (", .Length - 2, 2)
                            .Append("#", bytCount)
                            .Replace("#", "?,")
                            .Replace("?,", "?);", .Length - 2, 2)
                    End Select
                End With
            End If
            Return sbQuery.ToString
        End Function

        Private Sub ParamBuilder(ByRef Command As OLEDBCommand, ByVal Value As user_mstr_VO, Optional ByVal QueryType As QueryType = 0, Optional ByVal ExcludeValue As user_mstr_VO.Enum_user_mstr = user_mstr_VO.Enum_user_mstr.None)
            With Command.Parameters
                If Not isnothing(Value.USER_NAME) And Not CheckBit(ExcludeValue, user_mstr_VO.Enum_user_mstr.USER_NAME) Then .AddWithValue("@user_name", Value.USER_NAME)
                If Not isnothing(Value.USER_PASSWORD) And Not CheckBit(ExcludeValue, user_mstr_VO.Enum_user_mstr.USER_PASSWORD) Then .AddWithValue("@user_password", Value.USER_PASSWORD)
                If Not isnothing(Value.USER_LAST_NAME) And Not CheckBit(ExcludeValue, user_mstr_VO.Enum_user_mstr.USER_LAST_NAME) Then .AddWithValue("@user_last_name", Value.USER_LAST_NAME)
                If Not isnothing(Value.USER_FIRST_NAME) And Not CheckBit(ExcludeValue, user_mstr_VO.Enum_user_mstr.USER_FIRST_NAME) Then .AddWithValue("@user_first_name", Value.USER_FIRST_NAME)
                If Not isnothing(Value.USER_MIDDLE_NAME) And Not CheckBit(ExcludeValue, user_mstr_VO.Enum_user_mstr.USER_MIDDLE_NAME) Then .AddWithValue("@user_middle_name", Value.USER_MIDDLE_NAME)
                If Not isnothing(Value.DEPT_CD) And Not CheckBit(ExcludeValue, user_mstr_VO.Enum_user_mstr.DEPT_CD) Then .AddWithValue("@dept_cd", Value.DEPT_CD)
                If Not isnothing(Value.ROLE_CD) And Not CheckBit(ExcludeValue, user_mstr_VO.Enum_user_mstr.ROLE_CD) Then .AddWithValue("@role_cd", Value.ROLE_CD)
                If Value.ADVISOR_TAG >= 0 And Not CheckBit(ExcludeValue, user_mstr_VO.Enum_user_mstr.ADVISOR_TAG) Then .AddWithValue("@advisor_tag", Value.ADVISOR_TAG)
                If Not isnothing(Value.ADVISOR_LEVEL_CD) And Not CheckBit(ExcludeValue, user_mstr_VO.Enum_user_mstr.ADVISOR_LEVEL_CD) Then .AddWithValue("@advisor_level_cd", Value.ADVISOR_LEVEL_CD)
                If Not isnothing(Value.USER_ADDRESS) And Not CheckBit(ExcludeValue, user_mstr_VO.Enum_user_mstr.USER_ADDRESS) Then .AddWithValue("@user_address", Value.USER_ADDRESS)
                If Not isnothing(Value.USER_ADDRESS2) And Not CheckBit(ExcludeValue, user_mstr_VO.Enum_user_mstr.USER_ADDRESS2) Then .AddWithValue("@user_address2", Value.USER_ADDRESS2)
                If Not isnothing(Value.CITY_CD) And Not CheckBit(ExcludeValue, user_mstr_VO.Enum_user_mstr.CITY_CD) Then .AddWithValue("@city_cd", Value.CITY_CD)
                If Not isnothing(Value.COUNTRY_CD) And Not CheckBit(ExcludeValue, user_mstr_VO.Enum_user_mstr.COUNTRY_CD) Then .AddWithValue("@country_cd", Value.COUNTRY_CD)
                If Not isnothing(Value.ZIP_CD) And Not CheckBit(ExcludeValue, user_mstr_VO.Enum_user_mstr.ZIP_CD) Then .AddWithValue("@zip_cd", Value.ZIP_CD)
                If Not isnothing(Value.TEL_NO) And Not CheckBit(ExcludeValue, user_mstr_VO.Enum_user_mstr.TEL_NO) Then .AddWithValue("@tel_no", Value.TEL_NO)
                If Not isnothing(Value.FAX_NO) And Not CheckBit(ExcludeValue, user_mstr_VO.Enum_user_mstr.FAX_NO) Then .AddWithValue("@fax_no", Value.FAX_NO)
                If Not isnothing(Value.MOBILE_NO) And Not CheckBit(ExcludeValue, user_mstr_VO.Enum_user_mstr.MOBILE_NO) Then .AddWithValue("@mobile_no", Value.MOBILE_NO)
                If Not isnothing(Value.EMAIL_ADDRESS) And Not CheckBit(ExcludeValue, user_mstr_VO.Enum_user_mstr.EMAIL_ADDRESS) Then .AddWithValue("@email_address", Value.EMAIL_ADDRESS)
                If Not isnothing(Value.REMARKS) And Not CheckBit(ExcludeValue, user_mstr_VO.Enum_user_mstr.REMARKS) Then .AddWithValue("@remarks", Value.REMARKS)
                If Not isnothing(Value.USER_CREA_BY) And Not CheckBit(ExcludeValue, user_mstr_VO.Enum_user_mstr.USER_CREA_BY) Then .AddWithValue("@user_crea_by", Value.USER_CREA_BY)
                If Year(Value.USER_CREA_DT) > 1 And Not CheckBit(ExcludeValue, user_mstr_VO.Enum_user_mstr.USER_CREA_DT) Then .AddWithValue("@user_crea_dt", Value.USER_CREA_DT)
                If Not isnothing(Value.USER_LAST_UPD_BY) And Not CheckBit(ExcludeValue, user_mstr_VO.Enum_user_mstr.USER_LAST_UPD_BY) Then .AddWithValue("@user_last_upd_by", Value.USER_LAST_UPD_BY)
                If Year(Value.USER_LAST_UPD_DT) > 1 And Not CheckBit(ExcludeValue, user_mstr_VO.Enum_user_mstr.USER_LAST_UPD_DT) Then .AddWithValue("@user_last_upd_dt", Value.USER_LAST_UPD_DT)
            End With
        End Sub
        Public Function GetQuery(ByVal Value As user_mstr_VO, Optional ByVal ExcludeValue As user_mstr_VO.Enum_user_mstr = 0, Optional ByVal SQLLike As Boolean = False) As String
            Dim strReturn As String = "SELECT * FROM user_mstr " & QueryBuilder(Value, QueryType.SelectType, ExcludeValue, , SQLLike)
            Return strReturn
        End Function

    End Class

#End Region

#Region "fund_mstr"

    <Serializable()> _
    Public Class FUND_MSTR_VO

        Public FUND_CD As String
        Public PRDT_CD As String
        Public FUND_DESC As String
        Public CURRENCY_CD As String
        Public FUND_CREA_BY As String
        Public FUND_CREA_DT As Date
        Public FUND_CREA_DT_END As Date
        Public FUND_LAST_UPD_BY As String
        Public FUND_LAST_UPD_DT As Date
        Public FUND_LAST_UPD_DT_END As Date

        Public Enum Enum_FUND_MSTR As Short
            NONE = 0
            fund_cd = 1
            prdt_cd = 2
            fund_desc = 4
            currency_cd = 8
            fund_crea_by = 16
            fund_crea_dt = 32
            fund_crea_dt_END = 64
            fund_last_upd_by = 128
            fund_last_upd_dt = 256
            fund_last_upd_dt_END = 512
        End Enum

    End Class

    Public Class FUND_MSTR_DAO
        Inherits CommonDAO

        Public Function Create_Record(ByVal Value As fund_mstr_VO) As Boolean
            Dim daoMain As New Polydao.Common
            Dim cnnServer As New OleDbConnection
            Dim txnData As OleDbTransaction
            Dim blnSuccess As Boolean

            Try
                If IsNothing(Me.Transaction) Then
                    daoMain.ConnectionString = Me.ConnectionString
                    txnData = daoMain.OpenConnection(cnnServer)
                Else
                    txnData = Me.Transaction
                End If

                Dim cmdData As New OLEDBCommand

                With cmdData
                    .Connection = txnData.Connection
                    .Transaction = txnData
                    .CommandType = CommandType.StoredProcedure
                    .CommandText = "sp_fund_mstr_ins"
                End With

                'Dynamic Param Build up
                Call ParamBuilder(cmdData, Value, QueryType.InsertType)

                daoMain.UpdateData(cmdData)

                If IsNothing(Me.Transaction) Then txnData.Commit()
                blnSuccess = True

            Catch ex As Exception
                Debug.WriteLine(ex.Message)
                If IsNothing(Me.Transaction) Then txnData.Rollback()
                Throw ex

            Finally
                If IsNothing(Me.Transaction) Then daoMain.CloseConnection(cnnServer)

            End Try
            Return blnSuccess

        End Function

        Public Function Delete(ByVal Value As fund_mstr_VO) As Boolean
            Dim daoMain As New Polydao.Common
            Dim cnnServer As New OleDbConnection
            Dim txnData As OleDbTransaction
            Dim blnSuccess As Boolean

            Try
                If IsNothing(Me.Transaction) Then
                    daoMain.ConnectionString = Me.ConnectionString
                    txnData = daoMain.OpenConnection(cnnServer)
                Else
                    txnData = Me.Transaction
                End If

                Dim cmdData As New OLEDBCommand

                With cmdData
                    .Connection = txnData.Connection
                    .Transaction = txnData
                    .CommandText = QueryBuilder(Value, QueryType.DeleteType)
                End With

                'Dynamic Param Build up
                Call ParamBuilder(cmdData, Value, QueryType.DeleteType)

                daoMain.UpdateData(cmdData)

                If IsNothing(Me.Transaction) Then txnData.Commit()
                blnSuccess = True

            Catch ex As Exception
                Debug.WriteLine(ex.Message)
                If IsNothing(Me.Transaction) Then txnData.Rollback()
                Throw ex

            Finally
                If IsNothing(Me.Transaction) Then daoMain.CloseConnection(cnnServer)

            End Try
            Return blnSuccess

        End Function

        Public Overloads Function Update(ByVal Value As fund_mstr_VO) As Boolean
            Dim daoMain As New Polydao.Common
            Dim cnnServer As New OleDbConnection
            Dim txnData As OleDbTransaction
            Dim blnSuccess As Boolean

            Try
                If IsNothing(Me.Transaction) Then
                    daoMain.ConnectionString = Me.ConnectionString
                    txnData = daoMain.OpenConnection(cnnServer)
                Else
                    txnData = Me.Transaction
                End If

                Dim cmdData As New OLEDBCommand
                Dim sbQuery As New StringBuilder

                With cmdData
                    .Connection = txnData.Connection
                    .Transaction = txnData
                    .CommandType = CommandType.StoredProcedure
                    .CommandText = " sp_fund_mstr_upd"
                End With

                Call ParamBuilder(cmdData, Value, QueryType.UpdateType)

                daoMain.UpdateData(cmdData)

                If IsNothing(Me.Transaction) Then txnData.Commit()
                blnSuccess = True

            Catch ex As Exception
                Debug.WriteLine(ex.Message)

                If IsNothing(Me.Transaction) Then txnData.Rollback()

                Throw ex
            Finally
                If IsNothing(Me.Transaction) Then daoMain.CloseConnection(cnnServer)

            End Try

            Return blnSuccess
        End Function

        Public Function Search(ByVal Value As fund_mstr_VO, Optional ByVal ExcludeValue As fund_mstr_VO.Enum_fund_mstr = 0, Optional ByVal SQLLike As Boolean = False) As Data.Datatable
            Dim daoMain As New Polydao.Common
            Dim cnnSearch As New OleDbConnection
            Dim dtSearch As New Data.DataTable

            Try
                daoMain.ConnectionString = Me.ConnectionString
                cnnSearch = daoMain.OpenConnection

                Dim cmdSearch As New OLEDBCommand

                With cmdSearch
                    .Connection = cnnSearch
                    .CommandText = "SELECT * FROM fund_mstr " & QueryBuilder(Value, QueryType.SelectType, ExcludeValue, , SQLLike) & _
                                    " Order by Fund_Desc"
                End With

                Call ParamBuilder(cmdSearch, Value, ExcludeValue)

                Dim daSearch As New OleDbDataAdapter(cmdSearch)

                daSearch.Fill(dtSearch)

            Catch ex As Exception

                Debug.WriteLine(ex.Message)

            Finally
                daoMain.CloseConnection(cnnSearch)

            End Try

            Return dtSearch
        End Function

        Private Function QueryBuilder(ByVal Value As FUND_MSTR_VO, Optional ByVal QueryType As QueryType = 0, _
         Optional ByVal ExcludeFlag As FUND_MSTR_VO.Enum_fund_mstr = FUND_MSTR_VO.Enum_fund_mstr.NONE, Optional ByVal TableAlias As String = "", Optional ByVal SQLLike As Boolean = False, _
         Optional ByVal IncludeBlanks As Boolean = True) As String

            Dim sbQuery As New StringBuilder
            Dim shrtFlag As Short

            With Value
                If Not isnothing(.FUND_CD) Then shrtFlag += FUND_MSTR_VO.Enum_fund_mstr.FUND_CD
                If Not isnothing(.PRDT_CD) Then shrtFlag += FUND_MSTR_VO.Enum_fund_mstr.PRDT_CD
                If Not isnothing(.FUND_DESC) Then shrtFlag += FUND_MSTR_VO.Enum_fund_mstr.FUND_DESC
                If Not isnothing(.CURRENCY_CD) Then shrtFlag += FUND_MSTR_VO.Enum_fund_mstr.CURRENCY_CD
                If Not isnothing(.FUND_CREA_BY) Then shrtFlag += FUND_MSTR_VO.Enum_fund_mstr.FUND_CREA_BY
                If Year(.FUND_CREA_DT) > 1 Then shrtFlag += FUND_MSTR_VO.Enum_fund_mstr.FUND_CREA_DT
                If Year(.FUND_CREA_DT_END) > 1 Then shrtFlag += FUND_MSTR_VO.Enum_fund_mstr.FUND_CREA_DT_END
                If Not isnothing(.FUND_LAST_UPD_BY) Then shrtFlag += FUND_MSTR_VO.Enum_fund_mstr.FUND_LAST_UPD_BY
                If Year(.FUND_LAST_UPD_DT) > 1 Then shrtFlag += FUND_MSTR_VO.Enum_fund_mstr.FUND_LAST_UPD_DT
                If Year(.FUND_LAST_UPD_DT_END) > 1 Then shrtFlag += FUND_MSTR_VO.Enum_fund_mstr.FUND_LAST_UPD_DT_END
            End With
            If shrtFlag <> 0 Then
                Dim bytCount As Byte

                If ExcludeFlag <> FUND_MSTR_VO.Enum_fund_mstr.None Then shrtFlag -= ExcludeFlag
                With sbQuery

                    Select Case QueryType
                        Case QueryType.SelectType  ' Where
                            .Append("WHERE ")
                        Case QueryType.UpdateType  'Update
                            .Append("UPDATE fund_mstr SET ")
                        Case QueryType.InsertType  'Insert
                            .Append("INSERT INTO fund_mstr (")
                        Case QueryType.DeleteType   'Delete
                            .Append("DELETE FROM fund_mstr WHERE ")
                    End Select

                    Do While shrtFlag > 0
                        If CheckBit(shrtFlag, FUND_MSTR_VO.Enum_fund_mstr.FUND_CD) Then
                            .Append("FUND_CD")
                            shrtFlag -= FUND_MSTR_VO.Enum_fund_mstr.FUND_CD

                        ElseIf CheckBit(shrtFlag, FUND_MSTR_VO.Enum_fund_mstr.PRDT_CD) Then
                            .Append("PRDT_CD")
                            shrtFlag -= FUND_MSTR_VO.Enum_fund_mstr.PRDT_CD

                        ElseIf CheckBit(shrtFlag, FUND_MSTR_VO.Enum_fund_mstr.FUND_DESC) Then
                            .Append("FUND_DESC")
                            shrtFlag -= FUND_MSTR_VO.Enum_fund_mstr.FUND_DESC

                        ElseIf CheckBit(shrtFlag, FUND_MSTR_VO.Enum_fund_mstr.CURRENCY_CD) Then
                            .Append("CURRENCY_CD")
                            shrtFlag -= FUND_MSTR_VO.Enum_fund_mstr.CURRENCY_CD

                        ElseIf CheckBit(shrtFlag, FUND_MSTR_VO.Enum_fund_mstr.FUND_CREA_BY) Then
                            .Append("FUND_CREA_BY")
                            shrtFlag -= FUND_MSTR_VO.Enum_fund_mstr.FUND_CREA_BY

                        ElseIf CheckBit(shrtFlag, FUND_MSTR_VO.Enum_fund_mstr.FUND_CREA_DT) Then
                            If CheckBit(shrtFlag, FUND_MSTR_VO.Enum_fund_mstr.FUND_CREA_DT_END) Then
                                shrtFlag -= FUND_MSTR_VO.Enum_fund_mstr.FUND_CREA_DT
                                shrtFlag -= FUND_MSTR_VO.Enum_fund_mstr.FUND_CREA_DT_END
                                .Append(" FUND_CREA_DT between ? And ? ")
                                QueryType = 4
                            End If
                        ElseIf CheckBit(shrtFlag, FUND_MSTR_VO.Enum_fund_mstr.FUND_LAST_UPD_BY) Then
                            .Append("FUND_LAST_UPD_BY")
                            shrtFlag -= FUND_MSTR_VO.Enum_fund_mstr.FUND_LAST_UPD_BY

                        ElseIf CheckBit(shrtFlag, FUND_MSTR_VO.Enum_fund_mstr.FUND_LAST_UPD_DT) Then
                            If CheckBit(shrtFlag, FUND_MSTR_VO.Enum_fund_mstr.FUND_LAST_UPD_DT_END) Then
                                shrtFlag -= FUND_MSTR_VO.Enum_fund_mstr.FUND_LAST_UPD_DT
                                shrtFlag -= FUND_MSTR_VO.Enum_fund_mstr.FUND_LAST_UPD_DT_END
                                .Append(" FUND_LAST_UPD_DT between ? And ? ")
                                QueryType = 4
                            End If
                        End If

                        Select Case QueryType
                            Case QueryType.SelectType, QueryType.DeleteType  ' Where Clause
                                Select Case SQLLike
                                    Case False
                                        .Append(" = ? AND ")

                                    Case True
                                        .Append(" LIKE ? AND ")

                                End Select

                            Case QueryType.UpdateType  ' Update
                                .Append(" = ?, ")

                            Case QueryType.InsertType  ' Insert
                                .Append(", ")
                                bytCount += 1

                            Case Else 'bypass
                                .Append(" AND ")
                                QueryType = 0

                        End Select
                    Loop

                    Select Case QueryType
                        Case QueryType.SelectType, QueryType.DeleteType  'Where Clause
                            .Replace(" AND ", "", .Length - 6, 6)
                            '.Append(";")

                        Case QueryType.UpdateType   ' Update
                            .Replace("?, ", "? ", .Length - 3, 3)

                        Case QueryType.InsertType  ' Insert
                            .Replace(", ", ") VALUES (", .Length - 2, 2)
                            .Append("#", bytCount)
                            .Replace("#", "?,")
                            .Replace("?,", "?);", .Length - 2, 2)
                    End Select
                End With
            End If
            Return sbQuery.ToString
        End Function

        Private Sub ParamBuilder(ByRef Command As OLEDBCommand, ByVal Value As fund_mstr_VO, Optional ByVal QueryType As QueryType = 0, Optional ByVal ExcludeValue As fund_mstr_VO.Enum_fund_mstr = fund_mstr_VO.Enum_fund_mstr.None)
            With Command.Parameters
                If Not isnothing(Value.FUND_CD) And Not CheckBit(ExcludeValue, fund_mstr_VO.Enum_fund_mstr.FUND_CD) Then .AddWithValue("@fund_cd", Value.FUND_CD)
                If Not isnothing(Value.PRDT_CD) And Not CheckBit(ExcludeValue, fund_mstr_VO.Enum_fund_mstr.PRDT_CD) Then .AddWithValue("@prdt_cd", Value.PRDT_CD)
                If Not isnothing(Value.FUND_DESC) And Not CheckBit(ExcludeValue, fund_mstr_VO.Enum_fund_mstr.FUND_DESC) Then .AddWithValue("@fund_desc", Value.FUND_DESC)
                If Not isnothing(Value.CURRENCY_CD) And Not CheckBit(ExcludeValue, fund_mstr_VO.Enum_fund_mstr.CURRENCY_CD) Then .AddWithValue("@currency_cd", Value.CURRENCY_CD)
                If Not isnothing(Value.FUND_CREA_BY) And Not CheckBit(ExcludeValue, fund_mstr_VO.Enum_fund_mstr.FUND_CREA_BY) Then .AddWithValue("@fund_crea_by", Value.FUND_CREA_BY)
                If Year(Value.FUND_CREA_DT) > 1 And Not CheckBit(ExcludeValue, fund_mstr_VO.Enum_fund_mstr.FUND_CREA_DT) Then .AddWithValue("@fund_crea_dt", Value.FUND_CREA_DT)
                If Year(Value.FUND_CREA_DT_END) > 1 And Not CheckBit(ExcludeValue, fund_mstr_VO.Enum_fund_mstr.FUND_CREA_DT_END) Then .AddWithValue("@fund_crea_dt_END", Value.FUND_CREA_DT_END)
                If Not isnothing(Value.FUND_LAST_UPD_BY) And Not CheckBit(ExcludeValue, fund_mstr_VO.Enum_fund_mstr.FUND_LAST_UPD_BY) Then .AddWithValue("@fund_last_upd_by", Value.FUND_LAST_UPD_BY)
                If Year(Value.FUND_LAST_UPD_DT) > 1 And Not CheckBit(ExcludeValue, fund_mstr_VO.Enum_fund_mstr.FUND_LAST_UPD_DT) Then .AddWithValue("@fund_last_upd_dt", Value.FUND_LAST_UPD_DT)
                If Year(Value.FUND_LAST_UPD_DT_END) > 1 And Not CheckBit(ExcludeValue, fund_mstr_VO.Enum_fund_mstr.FUND_LAST_UPD_DT_END) Then .AddWithValue("@fund_last_upd_dt_END", Value.FUND_LAST_UPD_DT_END)
            End With
        End Sub

        Public Function GetQuery(ByVal Value As fund_mstr_VO, Optional ByVal ExcludeValue As fund_mstr_VO.Enum_fund_mstr = 0, Optional ByVal SQLLike As Boolean = False) As String
            Dim strReturn As String = "SELECT * FROM fund_mstr " & QueryBuilder(Value, QueryType.SelectType, ExcludeValue, , SQLLike)
            Return strReturn
        End Function

    End Class


#End Region



End Namespace
